import re
import traceback
import pymysql, json
from flask import Blueprint, request, current_app, send_file
import random
import openpyxl
import traceback
import os
from utilsapp.mysql_con import (
    db_connect,
    db_delete,
    db_insert,
    db_fetchall,
    db_list,
    db_fetchone,
    db_operation,
    db_table_pagelist,
    db_update,
    get_sensor_new_table,
)
from utilsapp import utils
import time
import pandas as pd
from datetime import datetime, timedelta
from utilsapp.common import parsing_data_id, assign_value
import math
import chardet
from io import BytesIO

eigenvalue = Blueprint("eigenvalue", __name__)
tableName = "eigenvalue"


@eigenvalue.route("/getlist", methods=["POST"])
def table_list():
    if "page" in request.json.keys() and "size" in request.json.keys():
        page = request.json["page"]
        size = request.json["size"]
    else:
        page = 1
        size = 999
    j = db_table_pagelist(tableName, page, "", size)
    return j


@eigenvalue.route("/add", methods=["POST"])
def eigenvalue_add():
    return db_insert(tableName, request.json)


@eigenvalue.route("/delete", methods=["POST"])
def eigenvalue_delete():
    id = request.json["id"]
    return db_delete(tableName, f"WHERE id=%s", {"id": id})


@eigenvalue.route("/edit", methods=["post"])
def eigenvalue_edit():
    id = request.json["id"]
    keys_not_add = ["id"]
    addModel = {
        key: request.json[key] for key in request.json if key not in keys_not_add
    }
    return db_update(tableName, addModel, f"WHERE id=%s", {"id": id})


# 获取指定sensor需要分析的特征值
@eigenvalue.route("/tags", methods=["get"])
def device_tags():
    id = int(request.values.get("id"))
    s0 = f"""		 SELECT e.id as id,e.name as value,unit,nickname  FROM eigenvalue e
			INNER JOIN sensor_type st on find_in_set(CAST(e.id as CHAR),st.eigenvalue)
			INNER JOIN sensor s on st.id=s.sensor_type
			where s.id={id}"""
    res = db_list(s0)
    sql_1 = f"""SELECT single from sensor_type st LEFT JOIN sensor s on 
    st.id = s.sensor_type where s.id = {id}"""
    single = db_fetchone(sql_1)["data"]
    # 判断传感器的特征值是否根据安装方向来确定
    if single == 1:
        sql_2 = f"""SELECT direction from sensor where id = {id}"""
        direction = db_fetchone(sql_2)["data"]
        data = res["data"]
        dir = ["轴向", "水平", "垂直"]
        if direction == "A":
            i = dir[0]
        elif direction == "H":
            i = dir[1]
        elif direction == "V":
            i = dir[2]
        filtered_data = []
        for d in data:
            value = d["value"]
            if any(value.find(c) != -1 for c in dir):
                if value.find(i) != -1:
                    filtered_data.append(d)
            else:
                filtered_data.append(d)
        res["data"] = filtered_data

    return res


# 获取测点特定区间的特征值
@eigenvalue.route("/sensor/getlist", methods=["post"])
def sensor_getlist():
    sensor_id = int(request.json.get("id"))
    startTime = str(
        time.strftime(
            "%Y-%m-%d %H:%M:%S",
            time.localtime(int(request.json.get("startTime")) / 1000),
        )
    )
    endTime = str(
        time.strftime(
            "%Y-%m-%d %H:%M:%S", time.localtime(int(request.json.get("endTime")) / 1000)
        )
    )

    feature_key = request.json.get("feature_key")

    yn = startTime.split(" ")[0][:7].replace("-", "")
    s0 = f"select * from sensor where id={sensor_id}"

    res = db_connect(s0)
    if not res.get("data"):
        return {"code": -1, "data": [], "msg": "数据为空!"}
    else:
        port = res.get("data")[0]["port"]
        address = res.get("data")[0]["address"]
        direction = res.get("data")[0]["direction"]
        if direction == "A":
            if feature_key[0] == "x":
                feature_key = feature_key.replace("x", "z")
            elif feature_key[0] == "z":
                feature_key = feature_key.replace("z", "x")
            else:
                pass
        elif direction == "H":
            if feature_key[0] == "y":
                feature_key = feature_key.replace("y", "z")
            elif feature_key[0] == "z":
                feature_key = feature_key.replace("z", "y")
            else:
                pass
        else:
            pass

        dbname = "d_" + port + "_" + address + "_" + yn

        s1 = f"select DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') as time,value from {dbname} where feature_key='{feature_key}' and time BETWEEN '{startTime}' and '{endTime}' order by time asc"

        try:
            res1 = db_connect(s1, "d_conn")

            return res1
        except:
            return {"code": -1, "data": [], "msg": "数据为空!"}


def count_num(port, address):
    now = datetime.now().strftime("%Y%m")
    year = now.year
    month = now.month
    dbname = "d_" + port + "_" + address + "_" + str(year) + str(month).zfill(2)
    sql_1 = f"select count(id) as num from {dbname} "
    num = db_connect(sql_1)["data"][0]["num"]
    while num <= 0:
        if month < 1:
            year = year - 1
        month = month - 1 if month > 1 else 12
        dbname = "d_" + port + "_" + address + "_" + str(year) + str(month).zfill(2)
        sql = f"select count(id) as num from {dbname} "
        num = db_connect(sql)["data"][0]["num"]

    return dbname



@eigenvalue.route("/lastest/ce", methods=["POST"])
# 平台暂未使用
def lastData():
    a = request.json
    sensor_id = assign_value(a, "sensor_id")
    number = assign_value(request.json, "number", 1)
    s0 = f"""select s.sensor_name,s.address,s.port,d.device_name,r.proj_no from sensor s
LEFT JOIN device d on s.device_id = d.id
LEFT JOIN regionlevel r on r.id = s.group_id
where s.id= {sensor_id}"""
    res = db_connect(s0)

    port = res.get("data")[0]["port"]
    address = res.get("data")[0]["address"]
    address = address if "_" in address else address + "_0"
    device_name = res.get("data")[0]["device_name"]
    sensor_name = res.get("data")[0]["sensor_name"]
    proj_no = res.get("data")[0]["proj_no"]
    mgdb = current_app.mgdb[f"ieplat_{proj_no}"]
    collection = mgdb["eige"]
    query = {"_id": {"$regex": f"^{port}_{address}"}}

    projection = {}
    result_data = collection.find(query, projection).sort([("_id", -1)]).limit(number)
    return {
        "code": 0,
        "data": {
            "id": result_data[0]["_id"][-10:],
            **result_data[0]["data"],
            "sensor_name": sensor_name,
            "device_name": device_name,
        },
    }


# 获取测点最新特征值数据
@eigenvalue.route("/latest", methods=["post"])
# 暂未使用
def eigenvalue_latest():
    sensor_id = int(request.json.get("id"))
    number = assign_value(request.json, "number", 500)
    yn = str(datetime.now()).split(" ")[0][:7].replace("-", "")
    s0 = f"""select s.* ,d.device_name from sensor s
LEFT JOIN device d on s.device_id = d.id
where s.id={sensor_id}"""
    res = db_connect(s0)
    if not res.get("data"):
        return {"code": -1, "data": [], "msg": "数据为空!"}
    else:
        port = res.get("data")[0]["port"]
        address = res.get("data")[0]["address"]
        device_name = res.get("data")[0]["device_name"]
        sensor_name = res.get("data")[0]["sensor_name"]
        dbname = get_sensor_new_table(sensor_id)
        if dbname is None:
            return utils.fail("暂无数据", "未找到最新数据")
        s1 = f"""SELECT DATE_FORMAT(time, '%Y-%m-%d %H:%i:%s') AS time, value, feature_key
FROM {dbname} 
WHERE time = (
    SELECT MAX(time)
    FROM {dbname} 
)"""
        res1 = db_connect(s1, _conn="conn")
        res1["device_name"] = device_name
        res1["sensor_name"] = sensor_name
        return res1


@eigenvalue.route("/recent", methods=["post"])
# 平台暂未使用
def sensor_recent():
    proj_no = request.json["proj_no"]
    saddr = request.json["saddr"]
    sport = request.json["sport"]
    eigkey = request.json["eigkey"]
    etime = int(time.time())
    stime = etime - 60 * 60 * 6
    mgdb = current_app.mgdb[f"ieplat_{proj_no}"]
    collection = mgdb[f"eige"]
    query = {
        "$and": [
            {"_id": {"$gte": f"{sport}_{saddr}_{str(stime)}"}},
            {"_id": {"$lte": f"{sport}_{saddr}_{str(etime)}"}},
        ]
    }
    projection = {}
    # 执行查询
    try:
        result = list(collection.find(query, projection))
        l = []
        for item in result:
            t = datetime.fromtimestamp(int(item["_id"].split("_")[3])).strftime(
                "%Y-%m-%d %H:%M:%S"
            )
            if "_x" in eigkey:
                value = item["data"]["X"][eigkey.split("_x")[0]]
            elif "_y" in eigkey:
                value = item["data"]["Y"][eigkey.split("_y")[0]]
            elif "_z" in eigkey:
                value = item["data"]["Z"][eigkey.split("_z")[0]]
            else:
                value = item["data"][eigkey]
            l.append({"time": t, "value": value})
        return utils.ok(l)
    except Exception as e:

        return utils.fail("暂无数据", "未找到最新数据")


@eigenvalue.route("/wave/tags", methods=["get", "POST"])
def wave_tags():
    sensor_id = int(request.values.get("id"))
    s0 = f"select st.feature_tag from sensor s left join sensor_type st on s.sensor_type=st.id where s.id={sensor_id}"
    res = db_connect(s0)
    if not res.get("data"):
        return {"code": -1, "data": [], "msg": "数据为空!"}
    else:
        temp = res.get("data")[0]["feature_tag"].split(";")
        ret = [
            {"id": item + 1, "value": temp[item], "key": temp[item][0]}
            for item in range(0, len(temp))
        ]
        res["data"] = ret
        return res


@eigenvalue.route("/wave/timelist", methods=["post"])
def wave_timelist():
    sensor_id = int(request.json.get("id"))
    startTime = str(
        time.strftime(
            "%Y-%m-%d %H:%M:%S",
            time.localtime(int(request.json.get("startTime")) / 1000),
        )
    )
    endTime = str(
        time.strftime(
            "%Y-%m-%d %H:%M:%S", time.localtime(int(request.json.get("endTime")) / 1000)
        )
    )
    yn = startTime.split(" ")[0][:7].replace("-", "")
    s0 = f"select * from sensor where id={sensor_id}"

    res = db_connect(s0)
    if not res.get("data"):
        return {"code": -1, "data": [], "msg": "数据为空!"}
    else:
        port = res.get("data")[0]["port"]
        address = res.get("data")[0]["address"]
        dbname = "s_" + port + "_" + address + "_" + yn
        s1 = f"select distinct DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') as time from {dbname} where time>'{startTime}' and time<'{endTime}'  order by time asc"
        try:
            res1 = db_connect(s1, _conn="conn")
            ll = len(res1.get("data"))
            for i in range(1, ll + 1):
                res1.get("data")[i - 1]["id"] = i
            return res1
        except:
            return {"code": -1, "data": [], "msg": "数据为空!"}


@eigenvalue.route("/wave/getlist", methods=["post"])
def wave_getlist():
    sensor_id = int(request.json.get("id"))
    timetag = str(request.json.get("time"))
    key = request.json.get("key")
    yn = timetag.split(" ")[0][:7].replace("-", "")
    s0 = f"select * from sensor where id={sensor_id}"
    res = db_connect(s0)
    if not res.get("data"):
        return {"code": -1, "data": [], "msg": "数据为空!"}
    else:
        if key in ["A", "B", "C"]:
            key = "current" + key
        else:
            key = "shock" + key
        port = res.get("data")[0]["port"]
        address = res.get("data")[0]["address"]
        dbname = "d_" + port + "_" + address + "_" + yn
        s1 = f"""select d.feature_key,d.value,f.feature_value,f.unit  from (select * from {dbname}  where time='{timetag}' and locate('{key}',feature_key)) d
        left join feature f on d.feature_key= f.feature_key
        """
        res1 = db_connect(s1, _conn="conn")
        return res1


@eigenvalue.route("wave/list", methods=["get"])
def a2():
    s0 = "select * from wave_tag"
    ret = db_connect(s0)
    return ret


@eigenvalue.route("eig/temps", methods=["post"])
def get_redis_data():
    r_key = request.json["r_key"]
    datas = current_app.redis.hgetall(r_key)
    return utils.ok(datas)


@eigenvalue.route("eig/savetempwarn", methods=["post"])
def set_warn_config():
    did = request.json["did"]
    sindex = request.json["sindex"]
    r_data = request.json["data"]
    if len(r_data) > 0:
        if sindex > 0:
            current_app.redis.hset(f"warn_config_sensor:{did}", sindex, r_data)
        else:
            current_app.redis.set(f"warn_config:{did}", r_data)
    d = (
        current_app.redis.hgetall(f"warn_config_sensor:{did}")
        if sindex > 0
        else current_app.redis.get(f"warn_config:{did}")
    )
    return utils.ok(d)


@eigenvalue.route("eig/savetempoffset", methods=["post"])
def set_warn_offset():
    r_key = request.json["did"]  # 设备id
    r_data = request.json["sindex"]  # 测点下标
    offset = request.json["offset"]  # 偏移量
    if len(offset) > 0:
        current_app.redis.hset(f"temp_config_offset:{r_key}", r_data, offset)
    d = current_app.redis.hgetall(f"temp_config_offset:{r_key}")
    return utils.ok(d)


@eigenvalue.route("eig/getstat", methods=["post"])
def get_stat():
    did = int(request.json["did"])  # 设备id
    sindex = int(request.json["sindex"])  # 测点下标
    dates = request.json["dates"]  # 2023-12-01,2023-12-15
    t = request.json["type"]  # 1,趋势 2,日平均值趋势
    zdh = str((did - 1) * 34 + math.ceil(sindex / 8)).rjust(4, "0")
    channel = (sindex - 1) % 8
    sqls = []
    sql = f"(SELECT ROUND( AVG(`value`->'$[{channel}]'),1) v, date(time) as d FROM d_5300_{zdh} WHERE  `time` BETWEEN '{dates[0]}' and '{dates[1]} 23:59:59'  GROUP BY d ORDER BY d)"
    if t == 1:
        sql = f"(SELECT `value`->'$[{channel}]' as v, `time` as d FROM d_5300_{zdh} WHERE  `time` BETWEEN '{dates[0]}' and '{dates[1]} 23:59:59'   ORDER BY d)"
    sqls.append(sql)
    sql = " UNION ALL ".join(sqls)
    return db_list(sql, _conn=True)


@eigenvalue.route("eig/gettypestat", methods=["post"])
def get_typestat():
    did = int(request.json["did"])  # 设备id
    dates = request.json["dates"]  # 2023-12-01,2023-12-15
    t = request.json["type"]  # 1 月趋势 2,日平均值趋势
    gc_index = [t for i in range(27) for t in [i * 4 + i + 1, i * 3 + i + 137]]
    gb_index = [
        t
        for i in range(27)
        for t in [i * 4 + i + 2, i * 4 + i + 3, i * 3 + i + 139, i * 3 + i + 138]
    ]
    ld_index = [
        t for i in range(27) for t in [i * 4 + i + 4, i * 4 + i + 5, i * 3 + i + 140]
    ]
    arr = [gc_index, gb_index, ld_index]
    results = []
    for at in arr:
        sqls = []
        for sindex in at:
            zdh = str((did - 1) * 34 + math.ceil(sindex / 8)).rjust(4, "0")
            channel = (sindex - 1) % 8
            sql = f"(SELECT `value`->'$[{channel}]' as v, year(time) as y,month(time) as m, date(`time`) as d FROM d_5300_{zdh} WHERE `time` BETWEEN '{dates[0]}' and '{dates[1]} 23:59:59')"
            sqls.append(sql)
        sql = " UNION ALL ".join(sqls)
        sql = (
            f"select ROUND(AVG(tb.v),1) v , CONCAT(tb.y,'-',tb.m) as d from ({sql}) tb  GROUP BY  tb.y,tb.m ORDER BY tb.y,tb.m"
            if t == 1
            else f"select ROUND(AVG(tb.v),1) v , tb.d from ({sql}) tb  GROUP BY  tb.d ORDER BY tb.d"
        )

        r = db_list(sql, _conn=True)["data"]
        results.append(r)
    return utils.ok(results)


@eigenvalue.route("/upload", methods=["POST"])
def upload():
    try:
        # 检查请求中是否包含文件
        if "file" not in request.files:
            return utils.fail("空文件")
        file = request.files["file"]
        # 检查文件是否为空
        if file.filename == "":
            return utils.fail("空文件")
        binary_data = file.read()
        result = chardet.detect(binary_data)
        encoding = result["encoding"]
        matches = re.findall(r'"([^"]*)"', binary_data.decode(encoding))
        cd1 = [257, 258, 259, 260, 261, 262, 265, 266, 267, 268, 269]
        cd2 = [249, 250, 251, 252, 253, 254, 255, 245, 246, 247, 248]
        id = int(file.filename.split("_")[1])
        for index, value in enumerate(matches):
            index = index + 1
            if index <= 27:
                current_app.redis.hset(
                    f"temp_config_offset:{id}", 5 * (index - 1) + 1, value
                )
            elif index <= 54:
                current_app.redis.hset(
                    f"temp_config_offset:{id}",
                    5 * (index - 1 - 27) + 2,
                    value.split("|")[0],
                )
                current_app.redis.hset(
                    f"temp_config_offset:{id}",
                    5 * (index - 1 - 27) + 3,
                    value.split("|")[1],
                )
            elif index <= 81:
                current_app.redis.hset(
                    f"temp_config_offset:{id}", 136 + (index - 1 - 54) * 4 + 1, value
                )
            elif index <= 108:
                current_app.redis.hset(
                    f"temp_config_offset:{id}",
                    136 + (index - 1 - 81) * 4 + 2,
                    value.split("|")[0],
                )
                current_app.redis.hset(
                    f"temp_config_offset:{id}",
                    136 + (index - 1 - 81) * 4 + 3,
                    value.split("|")[1],
                )
            elif index <= 135:
                current_app.redis.hset(
                    f"temp_config_offset:{id}", 5 * (index - 1 - 108) + 4, value
                )
            elif index <= 162:
                current_app.redis.hset(
                    f"temp_config_offset:{id}", 5 * (index - 1 - 135) + 5, value
                )
            elif index <= 189:
                current_app.redis.hset(
                    f"temp_config_offset:{id}", 136 + (index - 1 - 162) * 4 + 4, value
                )
            elif index <= 200:
                current_app.redis.hset(
                    f"temp_config_offset:{id}", cd1[index - 1 - 189], value
                )
            elif index <= 211:
                current_app.redis.hset(
                    f"temp_config_offset:{id}", cd2[index - 1 - 200], value
                )
        d = current_app.redis.hgetall(f"temp_config_offset:{id}")
        return utils.ok(d)
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log("测点导入", error_message)
        return utils.fail(None, "导入失败", e)


# 导出
@eigenvalue.route("batch/download", methods=["post"])
def excel_get():
    try:
        a = request.json
        zone_id = assign_value(a, "zone_id")
        date_time = assign_value(a, "date_time")
        start_time = date_time[0]
        end_time = date_time[1]
        id_get = f"""SELECT address,port FROM sensor WHERE zone_id = {zone_id}"""
        name_get = f"SELECT name as zone_name FROM regionlevel WHERE id  ={zone_id}"
        sensor_ids = db_fetchall(id_get)["data"]
        zone_name = db_fetchall(name_get)["data"][0][0]
        device_get = f"SELECT device_name FROM device WHERE zone_id = {zone_id}"
        device_names = db_fetchall(device_get)["data"]
        values = dict()
        tabel_name_end = "_" + start_time[:4] + start_time[5:7]
        for id in sensor_ids:
            table_name = "d_" + str(id[1]) + "_" + id[0] + tabel_name_end
            sql = f"""SELECT DATE_FORMAT(time, '%Y-%m-%d %H:00:00') AS hour,feature_key,
                       round(AVG(value),2) AS average_value FROM {table_name} WHERE feature_key in  ('temperature',
                        'xSpeed','ySpeed','zSpeed') and (time between '{start_time}' and '{end_time}') GROUP BY feature_key, hour"""
            sensor_values = db_fetchall(sql, _conn="conn")["data"]

            values[id[0]] = dict()
            for v in sensor_values:
                if v[0] not in values[id[0]].keys():
                    values[id[0]][v[0]] = list()
                    for feature in ["temperature", "xSpeed", "ySpeed", "zSpeed"]:
                        if v[1] == feature:
                            values[id[0]][v[0]].append(v[2])
                else:
                    for feature in ["temperature", "xSpeed", "ySpeed", "zSpeed"]:
                        if v[1] == feature:
                            values[id[0]][v[0]].append(v[2])

        endvalue = list()
        times = values[list(values.keys())[0]].keys()
        for time1 in times:
            temp_list = list()
            temp_list.append(time1)
            for sensor_id in values.keys():
                try:
                    temp_list = temp_list + values[sensor_id][time1]
                except:
                    temp_list = temp_list + [None, None, None, None]
            endvalue.append((temp_list))
        # 创建 Excel 工作簿
        workbook = openpyxl.Workbook()
        # # 获取默认的工作表
        worksheet = workbook.active
        # 设置表头的值和格式
        worksheet.merge_cells("B1:Q1")
        worksheet["B1"].value = device_names[0][0] if len(device_names) > 0 else ""
        worksheet.merge_cells("R1:AG1")
        worksheet["R1"].value = device_names[1][0] if len(device_names) > 1 else ""
        worksheet.merge_cells("AH1:AW1")
        worksheet["AH1"].value = device_names[2][0] if len(device_names) > 2 else ""
        worksheet.merge_cells("AX1:BM1")
        worksheet["AX1"].value = device_names[3][0] if len(device_names) > 3 else ""

        # 设置子表头的值
        sub_headers = [
            "时间",
            "电机自由端温度",
            "电机自由端水平速度",
            "电机自由端轴向速度",
            "电机自由端垂直速度",
            "电机输出端温度",
            "电机驱动端水平速度",
            "电机驱动端轴向速度",
            "电机驱动端垂直速度",
            "风机输入端温度",
            "风机输入端水平速度",
            "风机输入端轴向速度",
            "风机输入端垂直速度",
            "风机自由端温度",
            "风机自由端水平速度",
            "风机自由端轴向速度",
            "风机自由端垂直速度",
        ]

        headers = sub_headers + sub_headers[1:] + sub_headers[1:] + sub_headers[1:]
        for i, sub_header in enumerate(headers):
            worksheet.cell(row=2, column=i + 1).value = sub_header
        # 设置第三行的数据
        for j in range(len(endvalue)):
            for i, value in enumerate(endvalue[j]):
                worksheet.cell(row=j + 3, column=i + 1).value = value

        # 保存 Excel 文件

        date = time.strftime("%Y%m%d", time.localtime())
        script_dir = os.path.dirname(os.path.abspath(__name__))
        filePath = script_dir + "/static/upload/" + date
        if not os.path.exists(filePath):
            os.mkdir(filePath)
        filePath += f"/{zone_name}.xlsx"
        workbook.save(filePath)
        return send_file(
            filePath, as_attachment=True, attachment_filename=f"{zone_name}.xlsx"
        )

    except Exception as e:
        return {
            "code": -1,
            "data": [],
            "msg": str(e),
        }


# 实时数据
@eigenvalue.route("real/data", methods=["post"])
def asss():
    try:
        a = request.json
        topic = assign_value(a, "topic")
        if "7111" in topic:
            arr = topic.split("/")
            arr[2] = "6111"
            random_int = random.randint(1000, 1005)
            arr[3] = f"{random_int}"
            topic = "/".join(arr)
        data = current_app.redis.get(topic)
        if data is not None:
            data = data.replace("'", '"')
        return {"data": data, "code": 0}
    except Exception as e:
        return {
            "code": -1,
            "data": None,
            "msg": str(e),
        }
    
@eigenvalue.route("sql/data", methods=["post"])
def sql():
    try:
        a = request.json
        id = assign_value(a, "id")
        sensor = db_connect(f'select s.*,g.proj_no from sensor s LEFT JOIN regionlevel g on g.id = s.group_id where s.id = {id}')['data'][0]
        proj_no = sensor['proj_no']
        sport = sensor['port']
        saddr = sensor['address'].split('_')[0]
        channel = sensor['address'].split('_')[1] if len(sensor['address'].split('_')) == 2 else 0
        etime = int(datetime.now().timestamp())
        stime = int((datetime.now() - timedelta(minutes=120)).timestamp())
        collection = current_app.mgdb[f"ieplat_{proj_no}"][f"eige"]
        query = {"_id": {"$gte": f"{sport}_{saddr}_{channel}_{stime}", "$lte": f"{sport}_{saddr}_{channel}_{etime}"}}
        pipeline_latest = [
            {"$match": query},
            {"$sort": {"_id": -1}},  # 根据 _id 降序排序，使最新的数据在前面
            {"$project": {
                "_id": 1,
                "data": 1
            }},
            {"$limit": 1}
        ]
        result = list(collection.aggregate(pipeline_latest))
        return utils.ok(result)
    except Exception as e:
        return {
            "code": -1,
            "data": None,
            "msg": str(e),
        }
    

